package com.jinly.ryder.database.service.impl;

import com.jinly.ryder.common.error.SystemErrorType;
import com.jinly.ryder.database.dao.DbUtil;
import com.jinly.ryder.database.service.DatabaseExecService;
import com.jinly.ryder.database.service.model.QueryParam;
import com.jinly.ryder.database.service.model.QueryResult;
import com.jinly.ryder.database.service.model.TableColumn;
import com.jinly.ryder.manage.service.InstanceSecretService;
import com.jinly.ryder.manage.service.model.DatabaseInstance;
import com.jinly.ryder.manage.service.model.DatabaseType;
import com.jinly.ryder.manage.service.model.InstanceSecret;
import com.jinly.ryder.manage.service.model.InstanceType;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ConcurrentHashMap;


@Slf4j
@Service
@RequiredArgsConstructor
public class DatabaseExecServiceImpl implements DatabaseExecService {

    private final InstanceSecretService instanceSecretService;

    private Map<Integer, Connection> connectionMap = new ConcurrentHashMap<>();

    @Override
    public boolean testConnection(DatabaseInstance instance) {
        DatabaseType databaseType = instance.getType();
        String url = databaseType.getUrl(instance.getHost(), instance.getPort(), instance.getName());
        log.info("testConnection:{}:{}:{}", instance.getHost(), instance.getName(), instance.getUsername());
        try {
            DbUtil.initConnection(url, instance.getUsername(), instance.getPassword(), databaseType.getDriverClass());
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    public QueryResult execQuerySql(DatabaseInstance instance, QueryParam param) {
        Connection connection = getConnection(instance);
        return execQuerySql(connection, param, true);
    }

    @Override
    public void execSql(DatabaseInstance instance, String sql) {
        Connection connection = getConnection(instance);
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute(sql);
        } catch (Exception e) {
            log.error("执行sql[{}]失败", sql, e);
            SystemErrorType.INTERNAL_SERVER_ERROR.throwError(e.getMessage());
        } finally {
            DbUtil.close(statement);
        }
    }

    private Connection getConnection(DatabaseInstance instance) {
        Connection connection = connectionMap.get(instance.getId());
        if (connection != null) {
            try {
                if (connection.isValid(1000)) {
                    return connection;
                }
            } catch (Exception e) {
                log.error("获取数据库[{}]连接失败，将重新获取", instance.getName(), e);
            }
        }
        DatabaseType databaseType = instance.getType();
        String url = databaseType.getUrl(instance.getHost(), instance.getPort(), instance.getName());
        InstanceSecret instanceSecret = null;
        if (databaseType == DatabaseType.H2_LOCAL) {
            instanceSecret = new InstanceSecret();
            instanceSecret.setSecretName("sa");
        } else {
            instanceSecret = instanceSecretService.getPlainSecret(InstanceType.DATABASE, instance.getId());
        }
        if (instanceSecret == null) {
            SystemErrorType.INTERNAL_SERVER_ERROR.throwError("缺少账号和密码");
        }
        connection = DbUtil.initConnection(url, instanceSecret.getSecretName(), instanceSecret.getPlainText(), databaseType.getDriverClass());
        connectionMap.put(instance.getId(), connection);
        return connection;

    }

    private QueryResult execQuerySql(Connection connection, QueryParam param, boolean queryCount) {
        QueryResult queryResult = new QueryResult();
        BeanUtils.copyProperties(param, queryResult);
        long begin = System.currentTimeMillis();
        PreparedStatement statement = null;
        Statement countStatement = null;
        ResultSet resultSet = null;
        ResultSet countResultSet = null;
        try {
            boolean isLimitSql = param.getSql().toLowerCase().contains("limit ");
            boolean isSelectSql = param.getSql().toLowerCase().startsWith("select ");
            Long offset = (param.getPageNo() - 1) * param.getPageSize();
            String querySql = param.getSql();
            if (!isLimitSql && isSelectSql) {
                StringBuilder sql = new StringBuilder(querySql);
                sql.append(" limit ").append(offset).append(",").append(param.getPageSize());
                querySql = sql.toString();
            }
            statement = connection.prepareStatement(querySql);
            resultSet = statement.executeQuery();
            //字段名称
            ResultSetMetaData metaData = resultSet.getMetaData();
            List<TableColumn> columnList = new ArrayList<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                TableColumn column = new TableColumn();
                column.setColumnName(metaData.getColumnName(i));
                column.setColumnClassName(metaData.getColumnClassName(i));
                column.setColumnType(metaData.getColumnType(i));
                column.setColumnTypeName(metaData.getColumnTypeName(i));
                columnList.add(column);
            }
            queryResult.setColumnList(columnList);
            //查询数据
            List<Object[]> dataList = new ArrayList<>();
            while (resultSet.next()) {
                Object[] columnValues = new Object[columnList.size()];
                for (int i = 0; i < columnValues.length; i++) {
                    if (Objects.equals(columnList.get(i).getColumnTypeName(), "CLOB")) {
                        columnValues[i] = "不支持";
                    } else {
                        columnValues[i] = resultSet.getObject(i + 1);
                    }
                }
                dataList.add(columnValues);
            }
            queryResult.setDataList(dataList);

            //总数查询
            if (queryCount && isSelectSql) {
                String countSql = "select count(1) from (" + param.getSql() + ")t";
                countStatement = connection.createStatement();
                countResultSet = countStatement.executeQuery(countSql);
                while (countResultSet.next()) {
                    queryResult.setTotal(countResultSet.getLong(1));
                }
            }
        } catch (Exception e) {
            log.warn("查询异常{}:{}", e.getClass().getName(), param);
            queryResult.setSuccess(false);
            if (e instanceof SQLSyntaxErrorException) {
                queryResult.setMessage("查询异常:" + e.getMessage());
            }
            queryResult.setMessage("查询异常:" + e.getMessage());
        } finally {
            queryResult.setCost(System.currentTimeMillis() - begin);
            DbUtil.close(statement);
            DbUtil.close(countStatement);
            DbUtil.close(resultSet);
            DbUtil.close(countResultSet);
        }
        return queryResult;
    }
}
